Index maintenance

Index in the database

Objects are grouped in the database in collections - these are on the one hand the Root_Entry Point Collections of the ClassiX® system, on the other hand data fields of the objects of the type COLLECTION, REL_M1 or REL_MN.
Database indexes can be created for each collection (even several). The index refers to a data field of the objects contained in the collection or to a function.

An index means that

• objects with queries (which refer to the indexed data field / function) are found much faster
• the iteration with an index path (which refers to the indexed data field / function) becomes more effective.

A database index is automatically updated when the values of an affected data field change, when objects are removed from the indexed collection or new ones are inserted.

The following restrictions apply to the structure of a database index:

• Indexes can be created for objects from class CX_EXPANDABLE onwards. Only objects in this class and all classes derived from it have the ability to ensure that the index is actually updated automatically when the objects change.
  
• An index is then automatically updated when a data field touching the order within the index is changed with Put or DrainWindow.

  An index cannot be updated automatically if the connection to the affected object is lost, e.g. if value object Put(a.b) is replaced by object Get(a) -> x, value x Put(b) . This always applies to an index "a.b", but can also apply to an index "a" if b determines the order relation of a (-> concrete example).

• To save the effort of index updates for all those data fields where an index will probably never be created, the indexable data fields are marked in the data dictionaries(DDI). The reference to indexability can also be found in the CyberEnterprise® documentation.
  

• Indexes can also be created using dynamic data fields. The connection to the index mechanism of the database ObjectStore is complicated and must therefore be supported with specifications in the system dictionary - see directive Index.
  

• The type of data field over which an index is to be built must define an order relation. This is the case for STRING, INTEGER, SHORT etc.; as well as for all classes for which the comparison operators <, >and = are defined on the InstantView® level <, >(i.e. the class has the virtual function Compare).
  

• An index can only be built using those functions that are logged on to the ClassiX® system as query functions. They are always parameterless! This is a default of the database ObjectStore: The order relation should depend only on the state of the object. This means that an index has to be updated via a query function when certain data fields are changed. In order for this to happen automatically, the query function is entered in the DDI of the relevant data field(s) (-> concrete example).
  
  

• An index can also refer indirectly - via an access expression - to a data field - example: "owner.uniqueID".
  Compared to the possibilities of an access printout on the InstantView® level, restrictions apply when building a database index.
  

• An index via an InstantView® access printout can be created using the pseudo-function Retrieve. The above mentioned restrictions do not apply in this case. Such indexes are generally no longer updated automatically.
  The exception: The object(s) concerned have a PrePostUpdate function, which is linked to the corresponding data fields in the DDI and updates the index (-> concrete example).
  Otherwise, a "Retrieve(... )" index can be tracked explicitly with the Index Manager, i.e. the index update must be programmed in the InstantView® code.

Summary of how the ClassiX® system automates the update of a database index:

what is indexed...	so that an index is automatically updated ...
"normal" data field	Mark with INDEXABLE in the DDI
dynamic data field	Enter index(slotName, n) in the .ini file
Query function is dependent on "normal" data fields	specify QUERYFUNCTION(QueryFunction) in the DDI entry
complicated dependence of a retrieve index or an index via query function on certain data fields	in the DDI or (for dynamic data fields) in the .ini file PrePostUpdate function

A database index is created with the AddIndex statement. DropIndex statement removes a database index.
The Index Manager provides an overview of all indexes existing in the database.

Essential properties of a database index are specified by specifying options during construction:

Option	Meaning	further references
ORDERED	Index based on an order relation (<, > and = are defined)	without ORDERED only = must be defined, the index has the character of a hash table
NO_DUPLICATES	ObjectStore generates an error message if two index entries with the same value are created
COPY_KEY	the value of the data field is copied to the index	see below or directive IndexCopy in .ini file
POINT_TO_KEY	the value of the data field is not copied to the index, the index structure refers to the value in the object

The advantages and disadvantages of the COPY_KEY versus POINT_TO KEY options are

Option	Pro	Contra
COPY_KEY	high search speed, less locking conflicts	higher effort for index tracking, if the value of an affected data field changes
POINT_TO_KEY	lower search speed, higher probability of locking conflicts	slightly less effort for index tracking if the value of an affected data field changes

Conclusion:

For the database indexes possible in the ClassiX® system, the advantages of COPY_KEY outweigh the disadvantages !

What is copied with option COPY_KEY?

The COPY_KEY option instructs ObjectStore to copy the key data fields mentioned in the index. As far as objects are concerned, this is a flat copy. This is the default behavior.

The ClassiX® .ini file can be opened with

IndexCopy(DEEP)

deep copying is required when building the indexes.

Deep copying refers to

• to data fields of type STRING (represented by the class CXB_STRING). Now not only the CXB_STRING object but also the string itself is copied to the index. This improves the speed of queries and reduces locking conflicts.
• Indexes via dynamic data fields of the type "object" - e.g. CX_DATE, CX_TIME, CX_NUMERIC ...
  The internal handling is simplified without affecting performance and concurrency.

With IndexCopy(FLAT) you can configure that indices only copy their values flat.

If nothing is specified, IndexCopy(DEEP) is assumed.

Attention: before Dll version 212701 the behaviour was undefined if not specified.

Look out!
You can only switch FLAT -> DEEP or vice versa after you have deleted all database indexes. Only then may the .ini file be changed, and all indexes must be rebuilt. If this procedure is not followed, there is a risk of data loss!